package com.bolt.support.codegenerator.db;

import com.bolt.common.db.DbHelper;
import com.bolt.common.utils.StrUtil;
import com.bolt.convention.data.DefaultPageInfo;
import com.bolt.convention.data.PageInfo;
import com.bolt.convention.exception.ServiceException;
import com.bolt.support.codegenerator.dto.TableInfo;
import com.bolt.support.codegenerator.model.Table;
import com.bolt.support.sqlconfig.dialect.PagingMySQLDialect;

import java.sql.*;
import java.util.ArrayList;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Map;

/**
 * my sql 查询表名需要在jdbc　url 中加入useInformationSchema=true参数设置
 *
 * @author michao
 *         Created by Administrator on 2018/2/2.
 */
public class MysqlTableFactory extends AbstractTableFactory {

    private static final String TABLE_COMMENTS_SQL = "show table status where NAME=?";

    public MysqlTableFactory(String url, String username, String password, String driverClass) {
        super(url, username, password, driverClass);
    }

    @Override
    public Table getTable(String tableName) throws SQLException {
        Table table = super.getTable(tableName);
        if (table != null) {
            introspectTableComments(table, TABLE_COMMENTS_SQL, "COMMENT");
        }
        return table;
    }

    @Override
    public Map<String, String> getAllTables() throws SQLException {
        String sql = "select table_name ,create_time , engine, table_collation, table_comment from information_schema.tables " +
                "where table_schema = (select database())  order by create_time desc";

        Statement stmt = null;
        Connection connection = null;
        ResultSet rs = null;
        try {
            connection = getConnection();
            stmt = connection.createStatement();
            rs = stmt.executeQuery(sql);
            Map<String, String> tables = new LinkedHashMap<String, String>();
            while (rs.next()) {
                tables.put(rs.getString("table_name"), rs.getString("table_comment"));
            }
            return tables;
        } catch (SQLException e) {
            throw new ServiceException(e);
        } finally {
            DbHelper.close(rs);
            DbHelper.close(stmt);
            DbHelper.close(connection);
        }
    }

    @Override
    public PageInfo<TableInfo> getTables(String tableName, int page, int size) throws SQLException {
        String sql = "select table_name ,create_time , engine, table_collation, table_comment from information_schema.tables " +
                "where table_schema = (select database()) " +
                "and table_name like ? order by create_time desc ";

        PagingMySQLDialect MySQLDialect = new PagingMySQLDialect();
        String limitSql = MySQLDialect.getLimitString(sql, page * size, size);
        String countSql = MySQLDialect.getCountString(sql);
        List<TableInfo> datas = new ArrayList<>();
        PreparedStatement limitStmt = null;
        PreparedStatement countStmt = null;
        Connection connection = null;
        ResultSet limitRs = null;
        ResultSet countRs = null;
        long total = 0;
        try {
            connection = getConnection();
            limitStmt = connection.prepareStatement(limitSql);
            limitStmt.setString(1, StrUtil.isNotBlank(tableName) ? ("%" + tableName + "%") : "%%");

            limitRs = limitStmt.executeQuery();//执行查询操作
            while (limitRs.next()) {
                TableInfo tableInfo = new TableInfo();
                tableInfo.setTableName(limitRs.getString("table_name"));
                tableInfo.setRemark(limitRs.getString("table_comment"));
                tableInfo.setCreateTime(limitRs.getDate("create_time"));
                tableInfo.setCoding(limitRs.getString("table_collation"));
                tableInfo.setEngine(limitRs.getString("engine"));
                datas.add(tableInfo);
            }
            countStmt = connection.prepareStatement(countSql);
            countStmt.setString(1, StrUtil.isNotBlank(tableName) ? ("%" + tableName + "%") : "%%");
            countRs = countStmt.executeQuery();
            if (countRs.next()) {
                total = countRs.getLong(1);
            }
            return new DefaultPageInfo(page, size, datas, total);
        } catch (Exception e) {
            throw new ServiceException(e);
        } finally {
            DbHelper.close(limitRs);
            DbHelper.close(countRs);
            DbHelper.close(limitStmt);
            DbHelper.close(countStmt);
            DbHelper.close(connection);
        }
    }
}
